package crm.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import crm.dbcoon.DBConnect;
import crm.model.Material;
import crm.util.Pagination;

public class MaterialControl {
    public static Pagination pageInstance = null;
	
  //逐条添加资源信息
	public boolean insertMaterial(Material material){
		DBConnect dbc = new DBConnect();
		String sql = "insert into common_material (material_name,upload_time,sale_area_id,path) values (?,?,?,?)";
		dbc.prepareStatement(sql);
		try{
			dbc.setString(1, material.getMaterial_name());
			dbc.setString(2, material.getUpload_time());
			dbc.setInt(3, material.getSale_area_id());
			dbc.setString(4,material.getPath());
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println(sql);
			System.out.println("异常产生！insert");
			return false;
		}finally{
			dbc.close();
		}
	}
	
	//批量上传资源
	public boolean insertMaterial_many(Material material){
		DBConnect dbc = new DBConnect();
		String sql = "insert into common_material (name) values (?)";
		dbc.prepareStatement(sql);
		try{
			dbc.setString(1, material.getMaterial_name());
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("异常产生！");
			return false;
		}finally{
			dbc.close();
		}
	}
	
	//根据id更新资源
	public boolean updateMaterial(Material material,int id){
		DBConnect dbc = new DBConnect();
		String sql = "UPDATE common_material SET material_name=?,upload_time=?,sale_area_id=?,path=? WHERE id=?";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1,material.getMaterial_name());
			dbc.setString(2, material.getUpload_time());
			dbc.setInt(3, material.getSale_area_id());
			dbc.setString(4,material.getPath());
			dbc.setInt(5, id);
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("异常产生!update");
			return false;
		} finally {
			dbc.close();
		}	
	}
	
	//删除资源
	public boolean deleteMaterial(int id){
		DBConnect dbc = new DBConnect();
		String sql = "DELETE FROM common_material where id = ? ";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, id);
			dbc.executeUpdate();
			return true ;
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("异常产生！delete");
			return false;
		}finally{
			dbc.close();
		}
	}
	
	//获取资源信息
	public Material getMaterialInformation(int id){
		DBConnect dbc = new DBConnect();
		String sql = "SELECT * FROM common_material WHERE id = ?";
		dbc.prepareStatement(sql);
		try {
			Material material = new Material();
			dbc.setInt(1, id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			{
				material.setId(rs.getInt("id"));
				material.setMaterial_name(rs.getString("material_name"));
				material.setUpload_time(rs.getString("upload_time"));
				material.setSale_area_id(rs.getInt("sale_area_id"));
				material.setPath(rs.getString("path"));
				return material;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
		return null;
	}
	
	
	//默认按时间排序
	public Collection getMaterial(int page,int rowsPerPage)throws SQLException, Exception
	{
		String sql="SELECT * FROM common_material order by upload_time desc";
		pageInstance = new MaterialControlPage(sql, rowsPerPage);
		try {
			return pageInstance.getPage(page);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//名字查找信息
	public Collection getMaterial(int page,int rowsPerPage,String material_name)throws SQLException, Exception
	{
		String sql = "SELECT * FROM common_material where material_name = '"+material_name+"' order by upload_time desc";
		pageInstance = new MaterialControlPage(sql, rowsPerPage);
		try {
			return pageInstance.getPage(page);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
}
class MaterialControlPage extends Pagination {
	public MaterialControlPage(String sql, int rowsPerPage) {
		super(sql, rowsPerPage, 1);
	}
	@Override
	protected Collection<Material> packResultSet(ResultSet rs) throws SQLException {
		Collection<Material>MaterialList = new ArrayList<Material>();
		Material material = null;
		while (rs.next()) {
			material=new Material();
			material.setId(rs.getInt("id"));
			material.setMaterial_name(rs.getString("material_name"));
			material.setUpload_time(rs.getString("upload_time"));
			material.setSale_area_id(rs.getInt("sale_area_id"));
			material.setPath(rs.getString("path"));
			MaterialList.add(material);
			material=null;
		}
		return MaterialList;
	}

}
